package com.javaweb.dao.impl;

import com.javaweb.dao.ArticleDao;
import com.javaweb.model.Article;
import com.javaweb.model.Tag;
import com.javaweb.service.dto.ArticleConditionDto;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.util.Assert;
import org.springframework.util.ObjectUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

/**
 * @desc:
 * @author: hjm
 * @date: 2024/10/09 9:51
 */

@Repository
@Slf4j
public class ArticleDaoImpl implements ArticleDao  {

    @Autowired
    JdbcTemplate jdbcTemplate ;
    @Autowired
    private Connection conn;

    @Override
    public int insert(Article article) {
        // Assert.notNull(article)

        // 校验参数是否为null
        Assert.notNull(article, "Article must not be null");
        Assert.notNull(article.getUserId(), "User ID must not be null");
        Assert.notNull(article.getTitle(), "Title must not be null");
        Assert.notNull(article.getContent(), "Content must not be null");
        Assert.notNull(article.getCategoryId(), "Category ID must not be null");

        String sql = "INSERT INTO tb_article (user_id, title, content, status, " +
                "description, category_id, create_time, update_time)" +
                " VALUES (?,?,?,?,?,?,?,?)" ;
        Object [] args = new Object[]{
                article.getUserId(),
                article.getTitle(),
                article.getContent(),
                article.getStatus(),
                article.getDescription(),
                article.getCategoryId(),
                new java.sql.Date(article.getCreateTime().getTime()),
                new java.sql.Date(article.getUpdateTime().getTime())}                               ;
        try {
            // 执行插入操作
            int rowsAffected = jdbcTemplate.update(sql, args);
            return rowsAffected; // 返回影响的行数
        } catch (DataAccessException e) {
            // 记录异常信息
            log.error("Error inserting article", e);
            return 0; // 发生异常时返回0
        }
    }

    @Override
    public int updateContent(Integer id, String newContent) {
        if (id == null || newContent == null) {
            return 0;
        }
        String sql = "UPDATE tb_article SET content =? WHERE id =?";
        try {
            // 执行更新操作
            int rowsAffected = jdbcTemplate.update(sql, newContent, id);

            // 返回更新影响的行数，如果为0表示更新失败，返回1表示更新成功
            return rowsAffected > 0 ? 1 : 0;
        } catch (DataAccessException e) {
            // 记录异常信息
            log.error("Error updating article content", e);
            return 0; // 发生异常时返回0
        }
    }

    @Override
    public int updateTitle(Integer id, String newTitle) {
        if (id == null || newTitle == null) {
            return 0;
        }
        String sql = "UPDATE tb_article SET title =? WHERE id =?";
        try {
            // 执行更新操作
            int rowsAffected = jdbcTemplate.update(sql, newTitle, id);

            // 返回更新影响的行数，如果为0表示更新失败，返回1表示更新成功
            return rowsAffected > 0 ? 1 : 0;
        } catch (DataAccessException e) {
            // 记录异常信息
            log.error("Error updating article title", e);
            return 0; // 发生异常时返回0
        }
    }

    @Override
    public int deleteById(Integer id) {
        if (id == null) {
            return 0;
        }
        String sql = "DELETE FROM tb_article WHERE id =?";
        try {
            // 执行删除操作
            int rowsAffected = jdbcTemplate.update(sql, id);

            // 返回删除影响的行数，如果为0表示删除失败，返回1表示删除成功
            return rowsAffected > 0 ? 1 : 0;
        } catch (DataAccessException e) {
            // 记录异常信息
            log.error("Error deleting article with ID: {}", id, e);
            return 0; // 发生异常时返回0
        }
    }

    @Override
    public List<Article> selectByUserId(Integer userId) {
        if (userId == null) {
            return null;
        }
        String sql = "SELECT a.*, " +
                " t.id as tag_id, t.name as tag_name, t.create_time as tag_create_time, t.update_time as tag_update_time, " +
                " c.sup, c.opp " +
                " FROM tb_article a " +
                " LEFT JOIN tb_article_tag at ON a.id=at.article_id " +
                " LEFT JOIN tb_tag t ON t.id=at.tag_id " +
                " LEFT JOIN " +
                " ( SELECT article_id, " +
                " IFNULL(SUM(support), 0) AS sup, " +
                " IFNULL(SUM(oppose),0) AS opp " +
                " FROM tb_comment GROUP BY article_id) c " +
                " ON c.article_id=a.id " +
                " WHERE a.user_id = ?" ;

        return jdbcTemplate.query(sql, new Object[]{userId},
                rs -> {
                    Map<Integer, Article> articleMap = new HashMap<>();
                    while (rs.next()) {
                        Integer articleId = rs.getInt("id");
                        Article article = articleMap.get(articleId);
                        if (ObjectUtils.isEmpty(article)) {
                            article = new Article();
                            article.setId(articleId);
                            article.setTitle(rs.getString("title"));
                            article.setContent(rs.getString("content"));
                            article.setCreateTime(rs.getDate("create_time"));
                            article.setUpdateTime(rs.getDate("update_time"));
                            article.setTags(new ArrayList<>());
                            articleMap.put(articleId, article);
                        }

                        Integer tagId = rs.getInt("tag_id");
                        if (tagId != null) {
                            Tag tag = new Tag();
                            tag.setId(tagId);
                            tag.setName(rs.getString("tag_name"));
                            tag.setCreateTime(rs.getDate("tag_create_time"));
                            tag.setUpdateTime(rs.getDate("tag_update_time"));
                            article.getTags().add(tag);
                        }
                        article.setSupportCount(rs.getInt("sup")) ;
                        article.setOpposeCount(rs.getInt("opp")) ;
                    }
                    return new ArrayList<>(articleMap.values());
                });
    }


    @Override
    public int selectId(Integer userId, String title, Date createTime) {
        return 0;
    }

    @Override
    public List<Article> selectByKeyword(String keyword) {
        if (keyword == null) {
            return null;
        }
        String sql = "SELECT * FROM tb_article WHERE title LIKE ? OR content LIKE ? ORDER BY create_time DESC" ;
        List<Article> articles = jdbcTemplate.query(sql,new Object[]{"%" + keyword + "%","%" + keyword + "%"},
                new RowMapper<Article>() {
                    @Override
                    public Article mapRow(ResultSet rs, int rowNum) throws SQLException {
                        Article article = new Article();
                        article.setId(rs.getInt("id"));
                        article.setTitle(rs.getString("title"));
                        article.setContent(rs.getString("content"));
                        article.setStatus(rs.getInt("status")) ;
                        article.setDescription(rs.getString("description")) ;
                        article.setCategoryId(rs.getInt("category_id")) ;
                        article.setCreateTime(rs.getDate("create_time")) ;
                        article.setUpdateTime(rs.getDate("update_time")) ;
                        return article;

                    }
                });
        // 如果查询结果为空，返回null，否则返回查询结果
        return articles.isEmpty() ? null : articles;
    }

    @Override
    public List<Article> selectByTimePeriod(Date startTime, Date endTime) {
        // 构建SQL查询，处理startTime和endTime可能为null的情况
        String sql = "SELECT * FROM tb_article WHERE (create_time >= ? OR ? IS NULL) AND (create_time <= ? OR ? IS NULL) ORDER BY create_time DESC";
        Object [] args = new Object[]{startTime,startTime,endTime,endTime};

        List<Article> articles = jdbcTemplate.query(sql, args, new RowMapper<Article>() {
            @Override
            public Article mapRow(ResultSet rs, int rowNum) throws SQLException {
                Article article = new Article();
                article.setId(rs.getInt("id"));
                article.setTitle(rs.getString("title"));
                article.setContent(rs.getString("content"));
                article.setStatus(rs.getInt("status")) ;
                article.setDescription(rs.getString("description")) ;
                article.setCategoryId(rs.getInt("category_id")) ;
                article.setCreateTime(rs.getDate("create_time")) ;
                article.setUpdateTime(rs.getDate("update_time")) ;
                return article;
            }
        });
        // 如果查询结果为空，返回null，否则返回查询结果
        return articles.isEmpty() ? null : articles;
    }

    @Override
    public List<Article> selectByTag(Integer tagId) {
        String sql = "SELECT a.* FROM tb_article a JOIN tb_article_tag at ON a.id = at.article_id WHERE at.tag_id =?";
        return jdbcTemplate.query(sql, new Object[]{tagId},
                new RowMapper<Article>() {
                    @Override
                    public Article mapRow(ResultSet rs, int rowNum) throws SQLException {
                        Article article = new Article();
                        article.setId(rs.getInt("id"));
                        article.setTitle(rs.getString("title"));
                        article.setContent(rs.getString("content"));
                        article.setStatus(rs.getInt("status"));
                        article.setDescription(rs.getString("description"));
                        article.setCategoryId(rs.getInt("category_id"));
                        article.setCreateTime(rs.getDate("create_time"));
                        article.setUpdateTime(rs.getDate("update_time"));
                        return article;
                    }
                });
    }

    @Override
    public List<Article> selectByCategory(Integer categoryId) {
        String sql = "SELECT * FROM tb_article WHERE category_id =?";
        return jdbcTemplate.query(sql, new Object[]{categoryId},
                new RowMapper<Article>() {
                    @Override
                    public Article mapRow(ResultSet rs, int rowNum) throws SQLException {
                        Article article = new Article();
                        article.setId(rs.getInt("id"));
                        article.setTitle(rs.getString("title"));
                        article.setContent(rs.getString("content"));
                        article.setStatus(rs.getInt("status"));
                        article.setDescription(rs.getString("description"));
                        article.setCategoryId(rs.getInt("category_id"));
                        article.setCreateTime(rs.getDate("create_time"));
                        article.setUpdateTime(rs.getDate("update_time"));
                        return article;
                    }
                });
    }

    @Override
    public int selectCountByCondition(ArticleConditionDto articleCond) {
        String sql="select count(*) from tb_article";
        return jdbcTemplate.queryForObject(sql,new Object[]{},Integer.class);
    }

    @Override
    public List<Article> selectByArticleCondition(ArticleConditionDto articleCond) {
        String sql = "SELECT * FROM tb_article WHERE id like ?  and status like ? ";

        String column1 = articleCond.getId() ==null? "%%" : "%"+articleCond.getId() +"%";
        String column2= articleCond.getStatus() ==null? "%%" : "%"+ articleCond.getStatus() +"%";
        Object[] args = {column1, column2};
        return jdbcTemplate.query(sql,args,
                rs -> {
                    List<Article> list = new ArrayList<>();
                    while (rs.next()){
                        Article article = new Article();
                        article.setId(rs.getInt("id"));
                        article.setUserId(rs.getInt("user_id"));
                        article.setTitle(rs.getString("title"));
                        article.setContent(rs.getString("content"));
                        article.setStatus(rs.getInt("status"));
                        article.setDescription(rs.getString("description"));
                        article.setCategoryId(rs.getInt("catagory_id"));
                        article.setCreateTime(rs.getDate("create_time"));
                        article.setUpdateTime(rs.getDate("update_time"));
                        list.add(article);
                    }

                    return list;
                });
    }

    @Override
    public Article selectById(Integer id) {
        String sql = "SELECT * FROM tb_article WHERE id=?" ;
        Article article = new Article();
        try (PreparedStatement ps = this.conn.prepareStatement(sql);
             ResultSet rs = ps.executeQuery()) {
            if (rs.next()) {
                return article
                        .setId(id)
                        .setUserId(rs.getInt("user_id"))
                        .setTitle(rs.getString("title"))
                        .setContent(rs.getString("content"))
                        .setStatus(rs.getInt("status"))
                        .setDescription(rs.getString("description"))
                        .setCategoryId(rs.getInt("catagory_id"))
                        .setCreateTime(rs.getDate("create_time"))
                        .setUpdateTime(rs.getDate("update_time"));
            }
        } catch (SQLException e){}

        return article;
    }

    @Override
    public int updateStatus(Integer id, Integer status) {
        String sql = "UPDATE tb_article SET status = ? WHERE id=?" ;
        return jdbcTemplate.update(sql, new Object[]{status, id});
    }


}